package br.edu.ifrn.persistencia;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.edu.ifrn.beans.Convenio;

public class ConvenioDAO {
	Conexao conexao = Conexao.getInstance();

	public void inserirConvenio(Convenio c){ 
		PreparedStatement ps = null;
		System.out.println(c.getNome());
		try{
			String query = "INSERT INTO convenio VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			ps = conexao.getPreparedStatement(query);
			ps.setString(1, c.getCNPJ());
			ps.setString(2, c.getNome());
			ps.setString(3, c.getNomeFantasia());
			ps.setString(4, c.getLogradouro());
			ps.setString(5, c.getNumero());
			ps.setString(6, c.getBairro());
			ps.setString(7, c.getCep());
			ps.setString(8, c.getCidade());
			ps.setString(9, c.getUF());
			ps.setString(10, c.getComplemento());
			ps.setString(11, c.getTelefone());
			ps.setString(12, c.getEmail());
			ps.setString(13, c.getRepresentante());
			ps.setString(14, c.getCpfRepresentante());
			ps.setString(15, c.getSenha());
			ps.executeUpdate();
		} catch(SQLException ex){
			ex.printStackTrace();
		}
	}

	public void deletarConvenio(Convenio c){
		PreparedStatement ps = null;
		try{
			String query = "DELETE FROM convenio WHERE cnpj=?";
			ps = conexao.getPreparedStatement(query);
			ps.setString(1, c.getCNPJ());
			ps.executeUpdate();
		} catch(SQLException ex){
			ex.printStackTrace();
		}
	}

	public void editarConvenio(Convenio c){  
		PreparedStatement ps = null;
		try{
			String query = "UPDATE convenio SET nome=?, nomeFantasia=?, logradouro=?,numero=?," +
			"bairro=?, cep=?, cidade=?, uf=?, complemento=?, telefone=?, email=?, " +
			"representante=?, cpfRepresentante=? WHERE cnpj=?";
			ps = conexao.getPreparedStatement(query);
			ps.setString(1, c.getNome());
			ps.setString(2, c.getNomeFantasia());
			ps.setString(3, c.getLogradouro());
			ps.setString(4, c.getNumero());
			ps.setString(5, c.getBairro());
			ps.setString(6, c.getCep());
			ps.setString(7, c.getCidade());
			ps.setString(8, c.getUF());
			ps.setString(9, c.getComplemento());
			ps.setString(10, c.getTelefone());
			ps.setString(11, c.getEmail());
			ps.setString(12, c.getRepresentante());
			ps.setString(13, c.getCpfRepresentante());
			ps.setString(14, c.getCNPJ());

			ps.executeUpdate();
		} catch(SQLException ex){
			ex.printStackTrace();
		}
	}

	public List<Convenio> listarConvenios(){  
		List<Convenio> convenios = new ArrayList<Convenio>();
		PreparedStatement ps = null;
		try{
			String query = "SELECT * FROM convenio";
			ps = conexao.getPreparedStatement(query);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Convenio c = new Convenio();
				c.setCNPJ(rs.getString("cnpj"));
				c.setNome(rs.getString("nome"));
				c.setNomeFantasia(rs.getString("nomeFantasia"));
				c.setLogradouro(rs.getString("logradouro"));
				c.setNumero(rs.getString("numero"));
				c.setBairro(rs.getString("bairro"));
				c.setCep(rs.getString("cep"));
				c.setCidade(rs.getString("cidade"));
				c.setUF(rs.getString("uf"));
				c.setComplemento(rs.getString("complemento"));
				c.setTelefone(rs.getString("telefone"));
				c.setEmail(rs.getString("email"));
				c.setRepresentante(rs.getString("representante"));
				c.setCpfRepresentante(rs.getString("cpfRepresentante"));
				c.setSenha(rs.getString("senha"));
				convenios.add(c);
			}
		} catch (SQLException ex){
			ex.printStackTrace();
		}
		return convenios;
	}

	public Convenio recuperarPorCNPJ(String cnpj){ 
		PreparedStatement ps = null;
		Convenio c = new Convenio();
		try{
			String query = "SELECT * FROM convenio WHERE cnpj=?";
			ps = conexao.getPreparedStatement(query);
			ps.setString(1, cnpj);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				c.setCNPJ(rs.getString("cnpj"));
				c.setNome(rs.getString("nome"));
				c.setNomeFantasia(rs.getString("nomeFantasia"));
				c.setLogradouro(rs.getString("logradouro"));
				c.setNumero(rs.getString("numero"));
				c.setBairro(rs.getString("bairro"));
				c.setCep(rs.getString("cep"));
				c.setCidade(rs.getString("cidade"));
				c.setUF(rs.getString("uf"));
				c.setComplemento(rs.getString("complemento"));
				c.setTelefone(rs.getString("telefone"));
				c.setEmail(rs.getString("email"));
				c.setRepresentante(rs.getString("representante"));
				c.setCpfRepresentante(rs.getString("cpfRepresentante"));
				c.setSenha(rs.getString("senha"));
			}
		} catch(SQLException ex){
			ex.printStackTrace();
		}
		return c;
	}

	public void editarSenha(Convenio c){
		PreparedStatement ps = null;
		try{
			String query = "UPDATE convenio SET senha=? WHERE cnpj=?";

			ps = conexao.getPreparedStatement(query);

			ps.setString(1, c.getSenha());
			ps.setString(2, c.getCNPJ());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public Convenio logar(String id, String senha){
		Convenio c = recuperarPorCNPJ(id);
		if(senha.equals(c.getSenha()))
			return c;
		else
			return null;
			
	}

}
